package com.qshu.student.service.impl;

import com.qshu.student.entity.StudentInfo;
import com.qshu.student.service.studentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * username	String	用户名
 * password	String	用户密码
 * school	String	学校
 * grade	String	年级
 * clazz	String	班级
 * major	String	专业
 * phone	num	电话
 * idcard	String	身份证
 * bank	String	开户行
 * `stu_id  int 主键``school_id``cred_code  string 主键``stu_name``telephone``stu_code``grade_desc``class_desc``study_major``class_code``bank_code``bank_name`
 */
@Service
public class studentServiceimpl implements studentService {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List getAll() {

        return jdbcTemplate.queryForList("select * from tb_dm_student_info_make");
    }

    @Override
    public Map<String, Object> getOnebyId(int id) {
        return jdbcTemplate.queryForMap("select * from tb_dm_student_info_make where stu_id=?",id);
    }

    @Override
    public Map<String, Object> getOnebyCode(String credCode) {
        return jdbcTemplate.queryForMap("select * from tb_dm_student_info_make where cred_code=?",credCode);
    }

    @Override
    public int uptate(StudentInfo stf) {
        return jdbcTemplate.update("update tb_dm_student_info_make set school_id=?,cred_code=?,stu_name=,telephone=?,stu_code=?,grade_desc=?,study_major=?,class_code=?,bank_code=?,bank_name=?where stu_id=?",
                stf.getSchoolId(),stf.getCredCode(),stf.getStuName(),stf.getTelephone(),
                stf.getStuCode(),stf.getGradeDesc(),stf.getStudyMajor(),stf.getCredCode(),stf.getClassCode(),stf.getBankCode(),stf.getBankName(),stf.getStuId());
    }

    @Override
    public int delete(String id) {
        return jdbcTemplate.update("delete from tb_dm_student_info_make where stu_id=?", id);
    }

    @Override
    public List<String> getGrade() {
        List<Map<String,Object>> list= jdbcTemplate.queryForList("SELECT CONCAT(DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -rn YEAR),'%Y'),'级') CODE FROM" +
                "(SELECT @row_num:=@row_num+1 AS rn FROM (SELECT @row_num:=-1) r, tb_dmcode_school a LIMIT 4) a");
        List list1=new ArrayList();
        for (Map o :list) {
           list1.add(o.values());
        }
        return list1;
    }

    @Override
    public List getBankName() {
        return jdbcTemplate.queryForList("select * from bank");
    }

    @Override
    public int insert(StudentInfo stf) {
        return jdbcTemplate.update("insert into tb_dm_student_info_make (school_id,cred_code,stu_name,telephone,stu_code," +
                        "grade_desc,class_desc,study_major,class_code,bank_code,bank_name) values(?,?,?,?,?,?,?,?,?,?,?)",
                stf.getSchoolId(),stf.getCredCode(),stf.getStuName(),stf.getTelephone(),
                stf.getStuCode(),stf.getGradeDesc(),stf.getClassDesc(),stf.getStudyMajor(),
                stf.getClassCode(), stf.getBankCode(),stf.getBankName());
    }


}
